package com.skp.shaphan.ui.results;

import java.io.PrintWriter;
import java.io.StringWriter;
import java.net.URL;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.util.ResourceBundle;

import javafx.application.Platform;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.event.ActionEvent;
import javafx.event.EventHandler;
import javafx.fxml.FXML;
import javafx.fxml.Initializable;
import javafx.scene.Node;
import javafx.scene.control.ScrollBar;
import javafx.scene.control.TabPane;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.control.TextArea;
import javafx.scene.control.cell.MapValueFactory;
import javafx.scene.input.ScrollEvent;

import com.skp.job.ProjectJobException;
import com.skp.shaphan.ui.dialogs.DataExportDialog;
import com.skp.shaphan.ui.dialogs.ImportDialog;

public class ResultsController implements Initializable {
	private static final int INITIAL_ROW_MAX = 50;
	@FXML TableView<Row> resultTable;
	private ScrollBar bar = null;
	private ResultSet results = null;
	private String sqlStatement = null;
    private boolean finishedLoading;
    private ObservableList<Row> data;
	private int columnCount;
	@FXML TextArea txtMessages;
	@FXML TabPane tabs;
	
	public void setResultSet(String sqlStatement, final ResultSet results) {
		this.results = results;
		this.sqlStatement = sqlStatement;
		Platform.runLater(new Runnable() {

			@Override
	        public void run() {
	    		ObservableList<TableColumn<Row, ?>> columns = resultTable.getColumns();
				columns.clear();
	    		
				if(results == null) {
					resultTable.setItems(null);
					tabs.getSelectionModel().select(1);
					return;
				}
				tabs.getSelectionModel().select(0);
	    		
	    		data = FXCollections.observableArrayList();
				
				TableColumn<Row, String> tableColumn;
	    		
	    		tableColumn = new TableColumn<Row, String>(" ");
	    		tableColumn.setCellValueFactory(new MapValueFactory("rownum"));
				columns.add(tableColumn);
	    		try {
	    			ResultSetMetaData meta = results.getMetaData();
	    			columnCount = meta.getColumnCount();
					for(int x=1; x<=columnCount; x++) {
	    	    		tableColumn = new TableColumn<Row, String>(meta.getColumnLabel(x));
	    	    		tableColumn.setCellValueFactory(new MapValueFactory("col" + x));
						columns.add(tableColumn);
	    			}
	    			
	    			resultTable.setItems(data);

    				finishedLoading = false;
    				boolean broke = false;
	    			while(results.next()) {
	    				Row row = new Row(results, columnCount, data.size() + 1);
	    				data.add(row);
	    				if(data.size() >= INITIAL_ROW_MAX) {
	    					broke = true;
	    					break;
	    				}
	    			}
	    			if(!broke) {
	    				finishedLoading = true;
	    			}
	    			
	    		} catch (SQLException e) {
	    			(new ProjectJobException(e)).displayException();
	    			throw new Error("SQL Error: " + e.getLocalizedMessage(), e);
	    		}
	    		
	        }
	   });
	}
	
	public ResultSet getResultSet() {
		return results;
	}
	
	public String getCurrentSQLStatement() {
		return sqlStatement;
	}
	
	public void loadMoreRows() {
		System.out.println("Loading more rows!!!");
		if(finishedLoading) {
			return;
		}
		
		final ResultSet results = this.results;
		Platform.runLater(new Runnable() {

			@Override
	        public void run() {
				try {
					resultTable.setItems(data);

					boolean broke = false;
					while(results.next()) {
						Row row = new Row(results, columnCount, data.size() + 1);
						data.add(row);
						if(data.size() >= INITIAL_ROW_MAX) {
							broke = true;
							break;
						}
					}
					if(!broke) {
						finishedLoading = true;
					}

				} catch (SQLException e) {
					(new ProjectJobException(e)).displayException();
					throw new Error("SQL Error: " + e.getLocalizedMessage(), e);
				}
			}
		});
	}
	
	@Override
	public void initialize(URL url, ResourceBundle resources) {
		// http://stackoverflow.com/questions/13198881/javafx-scroll-events-handling-for-tableview
		resultTable.addEventFilter(ScrollEvent.ANY, new EventHandler<ScrollEvent>() {
			@Override
			public void handle(ScrollEvent e) {
				if(bar == null) {
					// https://forums.oracle.com/forums/thread.jspa?threadID=2388366
					for (Node n: resultTable.lookupAll(".scroll-bar")) {
						if (n instanceof ScrollBar) {
							if(((ScrollBar) n).getOrientation().ordinal() == 1) {
								bar = (ScrollBar) n;
							}
						}
					}

				}
				if(bar != null) {
					System.out.println("Scrolled!" + bar.getValue() + "/" +  bar.getMax() + "-->" + (bar.getValue() / bar.getMax() + " -- " + finishedLoading));
					if(bar.getValue() / bar.getMax() > 0.9) {
						if(!finishedLoading) {
							loadMoreRows();
						}
					}
				} else {
					System.err.println("bar scrolled, but not found!");
				}
			}
		});		
	}

	public void clearMessages() {
		txtMessages.clear();
	}
	
	public void setSQLMessages(ResultSet rs, Statement st, SQLException error) {
		StringWriter sw = new StringWriter();
		PrintWriter msg = new PrintWriter(sw);
		if(error != null) {
			msg.println("Error: " + error.getLocalizedMessage());
			msg.println("Detail: " + error.getMessage());
			error.printStackTrace(msg);
		}
		if(rs != null) {
			try {
				SQLWarning warning = rs.getWarnings();
				addSQLMessages(msg, warning);
			} catch (SQLException e) {
				msg.println("");
				msg.println("Error reading results warnings: " + e.getLocalizedMessage());
				e.printStackTrace(msg);
			}
		}
		if(st != null) {
			try {
				SQLWarning warning = st.getWarnings();
				addSQLMessages(msg, warning);
				warning = st.getConnection().getWarnings();
				addSQLMessages(msg, warning);
			} catch (SQLException e) {
				msg.println("");
				msg.println("Error reading statement warnings: " + e.getLocalizedMessage());
				e.printStackTrace(msg);
			}
		}
		
		txtMessages.appendText(sw.toString());
	}
	
	private void addSQLMessages(PrintWriter msg, SQLWarning warning) {
		while(warning != null) {
			msg.print("Warning -- Error Code; ");
			msg.print(warning.getErrorCode());
			msg.print("   ");
			msg.println(warning.getLocalizedMessage());
			warning = warning.getNextWarning();
		}
	}
	
	@FXML public void export(ActionEvent e) {
		//  Changed to use dialog created by Visual Swing
		//ExportDialog export = new ExportDialog();
		//export.setVisible(true);
		//  copied from ExportButton.java
		
		(new DataExportDialog()).setVisible(true);

	}
	
	@FXML public void importData(ActionEvent e) {
		//  copied from ImportButton.java
		ImportDialog i = new ImportDialog();
		i.setVisible(true);
	}
}
